# _*_ coding:utf-8 _*_
# @Time :2020/11/27 19:57
# @Author :yanxia
# _*_ coding:utf-8 _*_
# @Time :2020/11/2 22:58
# @Author :yanxia
import datetime
import time

from sqlalchemy import desc, func

from application.libs import const
from application.models.mantis_models import MantisBugTable, MantisCustomFieldStringTable
from application.models.models import *


class Bug_Calculate:
    # 暂时没地方用,且留~
    @classmethod
    def checkpoin_number(self):
        """
        查询检查点数量
        :return:
        """
        # sql = "select proname,versionname,checknum from bugcalculate order by addtime desc limit 30"
        result = db.session.query(Bugcalculate.proname, Bugcalculate.versionname, Bugcalculate.checknum).order_by(
            desc(Bugcalculate.addtime)).limit(30).all()
        check_num = []
        check_dic = {}
        for i in result:
            check_dic["proname"] = i.proname
            check_dic['versionname'] = i.versionname
            check_dic['checknum'] = i.checknum
            check_num.append(check_dic)
            check_dic.clear()
        return {"code": 20000, "data": check_num, "total": len(check_num)}

    @classmethod
    def insert_bug(self, vname, proname, versionname, name, checknum, fristnum, leaknum, newnum, bugcount, addtime):
        # bug密度
        if float(checknum) <= 1e-15:
            bugdensity = 0
        else:
            bugdensity = round(float(bugcount) / float(checknum), 2)
        # 首轮漏测率
        if bugcount is None or float(bugcount) <= 1e-15:
            fristleak = 0
        else:
            fristleak = round(float(leaknum) / float(bugcount), 2)
        # 引入错误率
        if float(fristnum) <= 1e-15 and float(leaknum) <= 1e-15:
            bringerror = 0
        else:
            bringerror = '%.2f' % (float(newnum) / (float(fristnum) + float(leaknum)))
        qulity_data = self.query_bugdata(vname, proname, versionname)
        if qulity_data:
            # sql = "update bugcalculate " \
            #       "SET name ='%s',checknum='%s',fristnum='%s',leaknum='%s',newnum='%s',bugcount='%s',bugdensity='%s',fristleak='%s',bringerror='%s',addtime='%s'" \
            #       " where vname ='%s'and proname='%s' and versionname='%s'" % (
            #       name, checknum, fristnum, leaknum, newnum, bugcount, bugdensity, fristleak, bringerror, addtime,
            #       vname, proname, versionname)
            db.session.query(Bugcalculate).filter(
                Bugcalculate.vname == vname, Bugcalculate.proname == proname,
                Bugcalculate.versionname == versionname).update(
                {"name": name,
                 "checknum": checknum,
                 "fristnum": fristnum,
                 "leaknum": leaknum,
                 "newnum": newnum,
                 "bugcount": bugcount,
                 "bugdensity": bugdensity, "fristleak": fristleak, "bringerror": bringerror, "addtime": addtime})
        else:
            # sql = "INSERT INTO bugcalculate (vname,proname,versionname,name,checknum,fristnum,leaknum,newnum,bugcount,bugdensity,fristleak,bringerror,addtime)" \
            #       "VALUES( '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
            #       vname, proname, versionname, name, checknum, fristnum, leaknum, newnum, bugcount, bugdensity,
            #       fristleak, bringerror, addtime)
            bugCalculate = Bugcalculate()
            bugCalculate.vname = vname
            bugCalculate.proname = proname
            bugCalculate.versionname = versionname
            bugCalculate.name = name
            bugCalculate.checknum = checknum
            bugCalculate.fristnum = fristnum
            bugCalculate.leaknum = leaknum
            bugCalculate.newnum = newnum
            bugCalculate.bugcount = bugcount
            bugCalculate.bugdensity = bugdensity
            bugCalculate.fristleak = fristleak
            bugCalculate.bringerror = bringerror
            bugCalculate.addtime = addtime
            db.session.add(bugCalculate)
        # db.session.execute(sql)
        try:
            db.session.commit()
        except:
            db.session.rollback()
            return False
        data_day = self.bugnewsel_day()
        if data_day:
            self.bugdel_day()  # 更新bug率本天数据
        else:
            self.bugnewinser_day()  # 新增本天bug率
        return True

    @classmethod
    def query_bugdata(self, vname, proname, versionname):
        """
        查询bug率数据是否存在
        :param vname:
        :param proname:
        :param versionname:
        :return:
        """
        # sql = "select bugdensity,fristleak,bringerror from bugcalculate where vname ='%s'AND proname='%s' AND versionname='%s'" % (
        #     vname, proname, versionname)
        # data = db.session.query(Bugcalculate.bugdensity, Bugcalculate.fristleak, Bugcalculate.bringerror).filter(
        #     [Bugcalculate.vname == vname, Bugcalculate.proname == proname,Bugcalculate.versionname == versionname]).all()
        data = db.session.query(Bugcalculate).filter(
            Bugcalculate.vname == vname, Bugcalculate.proname == proname,
            Bugcalculate.versionname == versionname).all()
        return data

    #
    @classmethod
    def bugnewsel_day(self):
        """
        判断是否有本天的bug率数据
        :return:
        """
        # sql = "SELECT * FROM `bugmidu_day` WHERE addtime= "
        # data = db.session.execute(sql).fetchall()
        data = db.session.query(BugmiduDay).filter(
            BugmiduDay.addtime == func.DATE_FORMAT(func.CURDATE(), '%Y-%m-%d')).all()
        return data

    @classmethod
    def get_end_date(self, beforeOfDay=28):
        """
        获取N天前的日期1
        :param beforeOfDay: 向前推的天数
        :return: N天前的日期
        """
        local_data = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        day = datetime.datetime.strptime(local_data, '%Y-%m-%d')
        # 计算偏移量
        offset = datetime.timedelta(days=-beforeOfDay)
        # 获取想要的日期的时间
        n_day = (day + offset).strftime('%Y-%m-%d')
        return n_day

    @classmethod
    def sql_query(self):
        """
        查询当天的数据2
        :param s_day: 开始时间
        :return: 当天-->28天前的日期  这个时间范围内所有的数据
        """
        s_day = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        e_day = self.get_end_date()
        # sql = "select id,bugdensity,fristleak,bringerror,addtime from bugcalculate where addtime between '{}' and '{}';".format(
        #     e_day, s_day)
        # datas = db.session.execute(sql).fetchall()

        datas = db.session.query(Bugcalculate.id, Bugcalculate.bugdensity, Bugcalculate.fristleak,
                                 Bugcalculate.bringerror,
                                 Bugcalculate.addtime).filter(Bugcalculate.addtime.between(e_day, s_day)).all()
        return datas

    @classmethod
    def bug_calc(self):
        """
        计算bug率数据
        :return:bug密度，首轮漏测率，引入错误率
        bug密度=round(sum(bugdensity)/count(proname),2)(保留两位小数)
        首轮漏测率=round(sum(fristleak)/count(proname),2)(保留两位小数)
        引入错误率=round(sum(bringerror)/count(proname),2)(保留两位小数)
        """
        datas = self.sql_query()
        bug_info = {}
        bug_01 = []
        bug_02 = []
        bug_03 = []
        for i in datas:
            # bug_01.append(i[1])
            # bug_02.append(i[2])
            # bug_03.append(i[3])
            bug_01.append(i.bugdensity)
            bug_02.append(i.fristleak)
            bug_03.append(i.bringerror)

        bug_01_sum = float(0)
        bug_02_sum = float(0)
        bug_03_sum = float(0)
        for x in bug_01:
            bug_01_sum += float(x)
        for y in bug_02:
            bug_02_sum += float(y)
        for z in bug_03:
            bug_03_sum += float(z)

        bug_info["bugdensity"] = round(bug_01_sum / len(bug_01), 2)
        bug_info["fristleak"] = round(bug_02_sum / len(bug_02), 2)
        bug_info["bringerror"] = round(bug_03_sum / len(bug_03), 2)
        bug_info["addtime"] = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        bug_info["updatetime"] = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        return bug_info

    @classmethod
    def bugdel_day(self):
        """
        更新bug率本天数据
        :return:
        """
        data = self.bug_calc()
        # sql = "update bugmidu_day SET `density`=%.2f, `leakage`=%.2f, `lead`=%.2f,`updatetime`='%s' WHERE addtime='%s'" % (
        #     data['bugdensity'], data['fristleak'], data['bringerror'],
        #     time.strftime('%Y-%m-%d', time.localtime(time.time())), data['addtime'])
        # db.session.execute(sql)
        db.session.query(BugmiduDay).filter(BugmiduDay.addtime == data['addtime']).update(
            {"density": data['bugdensity'],
             "leakage": data['fristleak'],
             "lead": data['bringerror'],
             "updatetime": time.strftime('%Y-%m-%d', time.localtime(time.time()))})
        try:
            db.session.commit()
        except:
            # 事務回滾
            db.session.rollback()

    @classmethod
    def bugnewinser_day(self):
        """
        新增本天bug率
        :return:
        """
        qua_data = self.bug_calc()
        # sql = "insert into bugmidu_day(addtime,density,leakage,lead,updatetime)VALUES('%s','%s','%s','%s','%s')" % (
        #     qua_data['addtime'], qua_data['bugdensity'], qua_data['fristleak'], qua_data['bringerror'],
        #     qua_data['updatetime'])
        # db.session.execute(sql)

        bugDensity = BugmiduDay()
        bugDensity.addtime = qua_data['addtime']
        bugDensity.density = qua_data['bugdensity']
        bugDensity.leakage = qua_data['fristleak']
        bugDensity.lead = qua_data['bringerror']
        bugDensity.updatetime = qua_data['updatetime']
        db.session.add(bugDensity)

        try:

            db.session.commit()
        except:
            db.session.rollback()

    @classmethod
    def get_bugdata(self, vname, proname, versionname):
        """
        查询bug密度、首轮漏测率、引入错误率
        :param vname:
        :param proname:
        :param versionname:
        :return:
        """
        # sql = "select bugdensity,fristleak,bringerror " \
        #       "from bugcalculate where vname ='%s'AND proname='%s' AND versionname='%s'" % (vname, proname, versionname)
        result = db.session.query(Bugcalculate.bugdensity, Bugcalculate.fristleak, Bugcalculate.bringerror).filter(
            Bugcalculate.vname == vname, Bugcalculate.proname == proname,
            Bugcalculate.versionname == versionname).all()
        data = {}
        for i in result:
            data["bugdensity"] = str(i.bugdensity)
            data["fristleak"] = str(i.fristleak)
            data["bringerror"] = str(i.bringerror)
        print(data)
        return data

    @classmethod
    def bugstatic_day(self):
        """
        查询bug率数据
        :return:
        """
        # sql = "select * from (select * from `bugmidu_day` where WeekDay(updatetime)  between 0 and 4 order by updatetime  DESC limit 60 )  aa ORDER BY id"

        a = db.session.query(BugmiduDay).filter(func.WeekDay(BugmiduDay.updatetime).between(0, 4)).order_by(
            desc(BugmiduDay.updatetime)).limit(30).subquery()
        result = db.session.query(a).order_by(a.c.id).all()
        density_list = []
        leakage_list = []
        lead_list = []
        bugDensity_dic = {}
        bugDensity_list = []
        for j in result:
            bugDensity_dic["date"] = str(j.addtime)
            bugDensity_dic["value"] = j.density
            density_list.append(bugDensity_dic.copy())
            bugDensity_dic["value"] = j.leakage
            leakage_list.append(bugDensity_dic.copy())
            bugDensity_dic["value"] = j.lead
            lead_list.append(bugDensity_dic.copy())
            bugDensity_dic.clear()
        bugDensity_dic['counter'] = density_list
        bugDensity_list.append(bugDensity_dic.copy())
        bugDensity_dic.clear()
        bugDensity_dic['counter'] = leakage_list
        bugDensity_list.append(bugDensity_dic.copy())
        bugDensity_dic.clear()
        bugDensity_dic['counter'] = lead_list
        bugDensity_list.append(bugDensity_dic.copy())
        bugDensity_dic.clear()
        return bugDensity_list

    @classmethod
    def calcu_param(self, data):
        """
        获取bug率计算的数据
        :return:
        """
        result_dic = {'bug_totals': 0, 'frist_bugs': 0, 'miss_bugs': 0, 'new_bugs': 0}
        result_bugs = db.session.query(func.count(MantisBugTable.id).label("bug_totals")).outerjoin(
            MantisCustomFieldStringTable, MantisCustomFieldStringTable.bug_id == MantisBugTable.id).filter(
            MantisBugTable.version == data['version_name'], MantisBugTable.project_id == data['project_id'],
            MantisBugTable.resolution != 60, MantisBugTable.resolution != 70, MantisBugTable.resolution != 90).all()

        if result_bugs:  # bug总数
            result_dic['bug_totals'] = result_bugs[0][0]
        result_frist_bugs = db.session.query(func.count(MantisBugTable.id).label("frist_bugs")).outerjoin(
            MantisCustomFieldStringTable, MantisCustomFieldStringTable.bug_id == MantisBugTable.id).filter(
            MantisCustomFieldStringTable.value == const.FRIST_BUG, MantisBugTable.version == data['version_name'],
            MantisBugTable.project_id == data['project_id'], MantisBugTable.resolution != 60,
            MantisBugTable.resolution != 70, MantisBugTable.resolution != 90).all()
        if result_frist_bugs:  # 首测bug数量
            result_dic['frist_bugs'] = result_frist_bugs[0][0]
        result_miss_bugs = db.session.query(func.count(MantisBugTable.id).label("miss_bugs")).outerjoin(
            MantisCustomFieldStringTable, MantisCustomFieldStringTable.bug_id == MantisBugTable.id).filter(
            MantisCustomFieldStringTable.value == const.MISS_BUG, MantisBugTable.version == data['version_name'],
            MantisBugTable.project_id == data['project_id'], MantisBugTable.resolution != 60,
            MantisBugTable.resolution != 70, MantisBugTable.resolution != 90).all()
        if result_miss_bugs:  # 漏测bug数量
            result_dic['miss_bugs'] = result_miss_bugs[0][0]
        result_new_bugs = db.session.query(func.count(MantisBugTable.id).label("new_bugs")).outerjoin(
            MantisCustomFieldStringTable, MantisCustomFieldStringTable.bug_id == MantisBugTable.id).filter(
            MantisCustomFieldStringTable.value == const.NEW_BUG, MantisBugTable.version == data['version_name'],
            MantisBugTable.project_id == data['project_id'], MantisBugTable.resolution != 60,
            MantisBugTable.resolution != 70, MantisBugTable.resolution != 90).all()
        if result_new_bugs:  # 新出现bug数量
            result_dic['new_bugs'] = result_new_bugs[0][0]
        return result_dic


